package com.travel.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.travel.pojo.dto.ResourceRankingDTO;
import com.travel.pojo.entity.Order;
import com.travel.pojo.vo.OrderTrendVO;
import com.travel.pojo.vo.ScenicRanking;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

@Mapper
public interface OrderMapper extends BaseMapper<Order> {
    @Select("<script>" +
            "SELECT " +
            "    DATE_FORMAT(created_at, '%m-%d') AS date, " +
            "    COUNT(CASE WHEN resource_type = 1 THEN 1 END) AS scenic_count, " +
            "    COUNT(CASE WHEN resource_type = 2 THEN 1 END) AS hotel_count " +
            "FROM orders " +
            "WHERE created_at BETWEEN #{startDate} AND #{endDate} " +
            "    AND status IN (2, 3, 4) " +
            "GROUP BY DATE(created_at), DATE_FORMAT(created_at, '%m-%d') " +
            "ORDER BY DATE(created_at)" +
            "</script>")
    List<OrderTrendVO> selectOrderTrend(
            @Param("startDate") Date startDate,
            @Param("endDate") Date endDate);


    /**
     * 景区订单排行榜（全部订单）
     *
     * @param limit 返回条数
     */
    @Select("SELECT r.id, r.name, COUNT(o.id) AS orderCount " +
            "FROM scenic_spots r " +
            "JOIN orders o ON o.resource_id = r.id " +
            "WHERE o.resource_type = 1 " +
            "AND o.status IN (2,3,4) " +  // 已支付、已使用、已评价
            "GROUP BY r.id, r.name " +
            "ORDER BY orderCount DESC " +
            "LIMIT #{limit}")
    List<ResourceRankingDTO> selectScenicRanking(@Param("limit") Integer limit);

    /**
     * 酒店订单排行榜（全部订单）
     */
    @Select("SELECT h.id, h.name, COUNT(o.id) AS orderCount " +
            "FROM hotels h " +
            "JOIN orders o ON o.resource_id = h.id " +
            "WHERE o.resource_type = 2 " +
            "AND o.status IN (2,3,4) " +
            "GROUP BY h.id, h.name " +
            "ORDER BY orderCount DESC " +
            "LIMIT #{limit}")
    List<ResourceRankingDTO> selectHotelRanking(@Param("limit") Integer limit);
}
